CREATE DATABASE [AutoTag]
GO

USE [AutoTag]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Schedule](
	[scheduleId] [bigint] IDENTITY(1,1) NOT NULL,
	[sTitle] [nvarchar](500) NOT NULL,
	[sDescription] [nvarchar](1000) NULL,
	[templateFileName] [nvarchar](450) NOT NULL,
	[reportFormat] [tinyint] NOT NULL,
	[runTime] [datetime] NOT NULL,
	[repeatType] [tinyint] NOT NULL,
	[runCycle] [tinyint] NOT NULL,
	[runPeriod] [tinyint] NOT NULL,
	[runDay] [tinyint] NULL,
	[runWeek] [tinyint] NULL,
	[runWeekday] [tinyint] NULL,
	[saveFolder] [nvarchar](500) NULL,
	[saveNum] [int] NULL,
	[emailTo] [nvarchar](4000) NULL,
	[revStamp] [timestamp] NOT NULL,
	[runLast] [datetime] NULL,
	[runResult] [nvarchar](500) NULL,
	[runNext] [datetime] NOT NULL,
	[timezone] [char](40) NOT NULL,
	[runCulture] [char](10) NOT NULL,
	[tSiteUrl] [varchar](500) NOT NULL,
	[system] [varchar](50) NOT NULL,
	[dspGuid] [nvarchar](500) NOT NULL,
	[isRptAsm] [nvarchar](5) NOT NULL,
 CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED 
(
	[scheduleId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'scheduleId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'title of this event - must be a legal filename' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'sTitle'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'description of the event' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'sDescription'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'filename of the template' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'templateFileName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Report.REPORT_TYPE of the report to create' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'reportFormat'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'local time - when to run the report first time. the day may not be correct as this is set by the user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'runTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SchedulePeriod.REPEAT' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'repeatType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'folder to use for saved reports when run' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'saveFolder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'maximum number of reports to save for this event (delete oldest)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'saveNum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'list of email addresses to email to, seperated by ;' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'emailTo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'show when edited' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'revStamp'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UTC - when last run, null if never run' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'runLast'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'output of last run. null or empty if successful' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'runResult'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UTC - time to run next' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'runNext'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UTC offset in minutes of creator of this event' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'timezone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'en-US type string of culture to run under (culture of creator)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Schedule', @level2type=N'COLUMN',@level2name=N'runCulture'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ScheduleVars](
	[scheduleId] [bigint] NOT NULL,
	[varName] [nvarchar](50) NOT NULL,
	[varText] [nvarchar](50) NULL,
	[varDate] [datetime] NULL,
	[varLong] [bigint] NULL,
	[varDouble] [float] NULL,
	[calOffset] [tinyint] NOT NULL,
	[offOffset] [int] NOT NULL CONSTRAINT [DF_ScheduleVars_offOffset]  DEFAULT ((0))
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleVars', @level2type=N'COLUMN',@level2name=N'scheduleId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of var' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleVars', @level2type=N'COLUMN',@level2name=N'varName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'value of var, ignored for some calOffset values' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleVars', @level2type=N'COLUMN',@level2name=N'varText'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ScheduleVarItem.CAL_OFFSET' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleVars', @level2type=N'COLUMN',@level2name=N'calOffset'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used yet. offset from calOffset start' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleVars', @level2type=N'COLUMN',@level2name=N'offOffset'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ScheduleRun](
	[ScheduleRunId] [bigint] IDENTITY(1,1) NOT NULL,
	[ScheduleId] [bigint] NOT NULL,
	[runTime] [datetime] NOT NULL CONSTRAINT [DF_ScheduleRun_runTime]  DEFAULT (getutcdate()),
	[runResult] [tinyint] NOT NULL,
	[runMessage] [nvarchar](4000) NULL,
	[reportFilename] [nvarchar](4000) NULL,
	[reportEmailTo] [nvarchar](4000) NULL,
 CONSTRAINT [PK_ScheduleRun] PRIMARY KEY CLUSTERED 
(
	[ScheduleRunId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleRun', @level2type=N'COLUMN',@level2name=N'ScheduleRunId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'PK of Schedule that ran this' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleRun', @level2type=N'COLUMN',@level2name=N'ScheduleId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'when run' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleRun', @level2type=N'COLUMN',@level2name=N'runTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ScheduleRunItem.RESULT' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleRun', @level2type=N'COLUMN',@level2name=N'runResult'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'error message when run, empty if successful' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScheduleRun', @level2type=N'COLUMN',@level2name=N'runMessage'
GO

ALTER TABLE [dbo].[ScheduleRun]  WITH NOCHECK ADD  CONSTRAINT [FK_ScheduleRun_Schedule] FOREIGN KEY([ScheduleId])
REFERENCES [dbo].[Schedule] ([scheduleId])
GO
ALTER TABLE [dbo].[ScheduleRun] CHECK CONSTRAINT [FK_ScheduleRun_Schedule]
GO

ALTER TABLE [dbo].[ScheduleVars]  WITH NOCHECK ADD  CONSTRAINT [FK_ScheduleVars_Schedule] FOREIGN KEY([scheduleId])
REFERENCES [dbo].[Schedule] ([scheduleId])
GO
ALTER TABLE [dbo].[ScheduleVars] CHECK CONSTRAINT [FK_ScheduleVars_Schedule]
GO
